package com.zoweunion.mechanic.sqlprovider;

import com.zoweunion.mechanic.util.constants.Constants;
import org.apache.commons.lang.StringUtils;

import java.util.List;
import java.util.Map;

/**
 * @Description:
 * @author: 王春伟
 * @date: 2019年1月7日 上午10:55:27
 * @Copyright: 2019 www.zoweunion.com Inc. All rights reserved.
 */
public class ShangJiSqlProvider {

	public String publish(Map<String,Object> map) {
		StringBuffer sql = new StringBuffer("INSERT INTO publish_shop_info (id,details,shop_name,opration_range,address,detail_address,contact,phone,image_name,thumFileName,create_user,create_time,update_user,update_time,longitude,latitude,province,city,area,type_name,delete_flag,browse,examine_flag,is_updated_browse,score,tel_consult,is_company_publish) VALUES(#{id},#{details},#{shop_name},#{opration_range},#{address},#{detail_address},#{contact},#{phone},#{image_name},#{thumFileName},#{create_user},now(),#{create_user},now(),#{longitude},#{latitude},#{province},#{city},#{area},#{type_name},0,0,0,0,5,0,");
        if(map.get("isCompany").equals('0')){
        	sql.append("0)");
		}else if(map.get("isCompany").equals('1')){
			sql.append("1)");
		}
        return sql.toString();
	}

	public String getShopList(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer();
		// 省市都为空，查询全国
		String columns = " * ";
		if (map.get("lat") != null && StringUtils.isNotBlank(map.get("lat").toString())) {
			columns += " , round(6378.138*2*asin(sqrt(pow(sin((#{lat}*pi()/180-latitude*pi()/180)/2),2)+cos(#{lat}*pi()/180)*cos(latitude*pi()/180)* pow(sin((#{lon}*pi()/180-longitude*pi()/180)/2),2)))*1000) as distance ";
		}
		if (map.get("isMap") != null && Boolean.valueOf(map.get("isMap").toString())) {
			columns = " id, shop_name, longitude, latitude, round(6378.138*2*asin(sqrt(pow(sin((#{lat}*pi()/180-latitude*pi()/180)/2),2)+cos(#{lat}*pi()/180)*cos(latitude*pi()/180)* pow(sin((#{lon}*pi()/180-longitude*pi()/180)/2),2)))*1000) as distance ";
		}

		sql.append("select " + columns + " from publish_shop_info where delete_flag=0 and examine_flag = 1");
		this.wrapShopReqParams(sql, map, 1);
		if (map.get("isMap") != null && Boolean.valueOf(map.get("isMap").toString())) {
			sql.append(" order by distance asc, create_time desc ");
		} else {
			if (map.get("lat") != null && StringUtils.isNotBlank(map.get("lat").toString())) {
				sql.append(" order by distance asc ");
			}
		}

		if (map.get("currentPage") != null && StringUtils.isNotBlank(map.get("currentPage").toString())) {
			sql.append(" limit #{currentPage}, #{pageSize}");
		}
		return sql.toString();
	}

	public String getShopListCount(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from publish_shop_info where delete_flag=0 and examine_flag = 1");
		this.wrapShopReqParams(sql, map, 1);
		return sql.toString();
	}

	public String getSortShopList(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer();
		// app端查询
		sql.append("select *,(st_distance(point(longitude,latitude),point(#{lon},#{lat}))) * 111195 AS distance from publish_shop_info where delete_flag = 0 and examine_flag = 1 ");
		this.wrapShopReqParams(sql, map, 1);
		sql.append(" order by create_time desc");
		sql.append(" limit #{currentPage},#{pageSize}");
		return sql.toString();
	}

	private StringBuffer wrapShopReqParams(StringBuffer sql, Map<String, Object> map, int level){
		if (map.get("shop_name") != null && !map.get("shop_name").equals("")) {
			sql.append(" and shop_name = #{shop_name}");
		}
		if (map.get("address") != null && !map.get("address").equals("")) {
			sql.append(" and address = #{address}");
		}
		// 市-省-国 查询
		this.wrapLevelParam(sql, map, level);
		if (map.get("type_name") != null && !map.get("type_name").equals("")) {
			sql.append(" and FIND_IN_SET(#{type_name},type_name)");
		}
		if (map.get("search_shop_name") != null && StringUtils.isNotBlank(map.get("search_shop_name").toString())) {
			sql.append(" and shop_name like concat('%', #{search_shop_name}, '%')");
		}
		if (map.get("contact") != null && StringUtils.isNotBlank(map.get("contact").toString())) {
			sql.append(" and contact like concat('%', #{contact}, '%')");
		}
		if (map.get("phone") != null && StringUtils.isNotBlank(map.get("phone").toString())) {
			sql.append(" and phone like concat('%', #{phone}, '%')");
		}
		return sql;
	}

	private StringBuffer wrapLevelParam(StringBuffer sql, Map<String, Object> map, int level) {
		// 市-省-国 查询
		if (level == 1) {
			if (map.get("province") != null && !map.get("province").equals("")) {
				sql.append(" and province = #{province}");
			}
			if (map.get("city") != null && !map.get("city").equals("")) {
				sql.append(" and city = #{city}");
			}
		} else if (level == 2) {
			if (map.get("province") != null && !map.get("province").equals("")) {
				sql.append(" and province = #{province}");
			}
			if (map.get("city") != null && !map.get("city").equals("")) {
				sql.append(" and city != #{city}");
			}
		} else {
			if (map.get("province") != null && !map.get("province").equals("")) {
				sql.append(" and province != #{province}");
			}
			if (map.get("city") != null && !map.get("city").equals("")) {
				sql.append(" and city != #{city}");
			}
		}
		return sql;
	}

	public String getShopInfo(Map<String, Object> map) {

		StringBuffer sql = new StringBuffer("select * from publish_shop_info where ");
		sql.append(" id = #{id}");
		return sql.toString();
	}

	public String updateShopBrowse(Map<String, Object> map) {

		StringBuffer sql = new StringBuffer("update publish_shop_info set browse = (browse + FLOOR(60 + RAND() * (80 - 60))) ");
		sql.append(" where id = #{id}");
		return sql.toString();
	}

	public String updateShopExamineFlag(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("update publish_shop_info set examine_flag = #{examine_flag}");
		if ("1".equals(map.get("examine_flag").toString())) {
			sql.append(", browse=FLOOR(100 + RAND() * (300 - 100)) ");
		}
		sql.append(", update_user = #{update_user}, update_time = #{update_time} where delete_flag = 0");
		if (map.get("ids") != null) {
			sql.append(" and find_in_set(id, #{ids}) ");
		}
		if (map.get("hour") != null && StringUtils.isNotBlank(map.get("hour").toString())) {
			sql.append(" and examine_flag = 0 ");
			sql.append(" and UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(publish_time) >= #{hour}");
		}
		return sql.toString();
	}

	public String getzhongbiaoInfo(Map<String, Object> map) {

		StringBuffer sql = new StringBuffer("select * from zowe_project_detail where ");
		sql.append(" id = #{id}");
		return sql.toString();
	}

	public String getzhongbiaoInfoNew(Map<String, Object> map) {

		StringBuffer sql = new StringBuffer(
				"select a.subject, a.amount, a.labels, a.url, a.createTime, b.param_key, b.param_value from zowe_project a " +
						" left join zowe_project_detail_new b on a.id = b.id where a.delete_flag=0 ");
		sql.append(" and a.id = #{id}");
		return sql.toString();
	}

	public String getzhongbiaoList(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from zowe_project where delete_flag = 0");
		this.wrapzhongbiaoParams(sql, map, 1);

		sql.append(" order by createTime desc");

		sql.append(" limit #{currentPage},#{pageSize}");
		return sql.toString();
	}

	private StringBuffer wrapzhongbiaoParams(StringBuffer sql, Map<String, Object> map, int level){
		// 市-省-国 查询
		this.wrapLevelParam(sql, map, level);
		if (map.get("dataType") != null && !"0".equals(map.get("dataType").toString())) {
			sql.append(" and dataType = #{dataType}");
		}
		if (map.get("startTime") != null) {
			sql.append(" and createTime >= #{startTime}");
		}
		if (map.get("endTime") != null) {
			sql.append(" and createTime <= #{endTime}");
		}
		return sql;
	}

	public String getpublishCarsList(Map<String, Object> map) {

		StringBuffer sql = new StringBuffer("select * from publish_cars_info where delete_flag = 0");
		if (map.get("servece_type") != null) {
			sql.append(" and servece_type = #{servece_type}");
		}
		if (map.get("equipment_address") != null) {
			sql.append(" and equipment_address = #{equipment_address}");
		}
		if (map.get("equipment_type") != null) {
			sql.append(" and equipment_type = #{equipment_type}");
		}
		if (map.get("leave_factory_time") != null) {
			sql.append(" and leave_factory_time = #{leave_factory_time}");
		}
		if (map.get("age_limit") != null) {
			sql.append(" and age_limit = #{age_limit}");
		}
		if (map.get("expect_price") != null) {
			sql.append(" and expect_price = #{expect_price}");
		}
		if (map.get("contact") != null) {
			sql.append(" and contact = #{contact}");
		}
		if (map.get("phone") != null) {
			sql.append(" and phone = #{phone}");
		}
		if (map.get("createTime") != null) {
			sql.append(" and createTime = #{createTime}");
		}
		sql.append(" order by create_time desc");
		sql.append(" limit #{currentPage},#{pageSize}");
		return sql.toString();
	}

	public String publishCars() {

		return "INSERT INTO publish_cars_info VALUES(#{id},#{shop_id},#{servece_type},#{equipment_address},#{equipment_type},#{used_hours},#{leave_factory_time},#{age_limit},#{expect_price},#{contact},#{phone},#{remark},#{create_user},now(),#{update_user},now(),0";

	}

	public String publishBuyerInfo() {

		return "INSERT INTO publish_buyer_cars_info VALUES(#{id},#{servece_type},#{jop_address},#{equipment_type},#{project_limit_time},#{least_available_jop_time},#{expect_much},#{pay_type},#{company_name},#{phone},#{remark},#{create_user},now(),#{update_user},now(),0,#{contact}";

	}

	public String getpublishBuyerInfoList(Map<String, Object> map) {

		StringBuffer sql = new StringBuffer("select * from publish_buyer_cars_info where delete_flag = 0");
		if (map.get("servece_type") != null) {
			sql.append(" and servece_type = #{servece_type}");
		}
		if (map.get("jop_address") != null) {
			sql.append(" and jop_address = #{jop_address}");
		}
		if (map.get("equipment_type") != null) {
			sql.append(" and equipment_type = #{equipment_type}");
		}
		if (map.get("project_limit_time") != null) {
			sql.append(" and project_limit_time = #{project_limit_time}");
		}
		if (map.get("least_available_jop_time") != null) {
			sql.append(" and least_available_jop_time = #{least_available_jop_time}");
		}
		if (map.get("expect_much") != null) {
			sql.append(" and expect_much = #{expect_much}");
		}
		if (map.get("pay_type") != null) {
			sql.append(" and pay_type = #{pay_type}");
		}
		if (map.get("company_name") != null) {
			sql.append(" and company_name = #{company_name}");
		}
		if (map.get("phone") != null) {
			sql.append(" and phone = #{phone}");
		}
		if (map.get("contact") != null) {
			sql.append(" and contact = #{contact}");
		}
		return sql.toString();
	}

	public String publishJoberInfo(Map<String,Object> map) {
		StringBuffer sql = new StringBuffer("INSERT INTO publish_jober_info (id,name,phone,job_status,equipment_type,drive_age,project_type,opreation_direction,salary,remark,create_user,create_time,update_user,update_time,delete_flag,job_address,province,city,area,arrival_time,work_time,subject,type_name,browse,education,work_address,examine_flag,is_updated_browse,tel_consult,is_company_publish) VALUES(#{id},#{name},#{phone},#{job_status},#{equipment_type},#{drive_age},#{project_type},#{opreation_direction},#{salary},#{remark},#{create_user},now(),#{update_user},now(),0,#{job_address},#{province},#{city},#{area},#{arrival_time},#{work_time},#{subject},#{type_name},0,#{education},#{work_address},0,0,0,");
		if(map.get("isCompany").equals('0')){
			sql.append("0)");
		}else if(map.get("isCompany").equals('1')){
			sql.append("1)");
		}
		return sql.toString();

	}

	public String updateJoberExamineFlag(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("update publish_jober_info set examine_flag = #{examine_flag}");
		if ("1".equals(map.get("examine_flag").toString())) {
			sql.append(", browse=FLOOR(100 + RAND() * (300 - 100)) ");
		}
		sql.append(", update_user = #{update_user}, update_time = #{update_time} where delete_flag = 0");
		if (map.get("ids") != null) {
			sql.append(" and find_in_set(id, #{ids}) ");
		}
		if (map.get("hour") != null && StringUtils.isNotBlank(map.get("hour").toString())) {
			sql.append(" and examine_flag = 0 ");
			sql.append(" and UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(publish_time) >= #{hour}");
		}
		return sql.toString();
	}

	public String getJoberInfoList(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from publish_jober_info where delete_flag = 0 and examine_flag = 1 ");
		this.wrapJoberInfoReqParams(sql, map, 1);
		if (map.get("filter") != null) {
			if ("0".equals(map.get("filter").toString())) {
				sql.append(" order by create_time desc");
			} else if ("1".equals(map.get("filter").toString())) {
				sql.append(" order by browse desc");
			} else if ("2".equals(map.get("filter").toString())) {
				sql.append(" order by create_time desc");
			}
		} else {
			sql.append(" order by create_time desc");
		}
		sql.append(" LIMIT #{currentPage}, #{pageSize}");
		return sql.toString();
	}

	public String getJoberInfoCount(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) from publish_jober_info where delete_flag = 0 and examine_flag = 1 ");
		this.wrapJoberInfoReqParams(sql, map, 1);
		return sql.toString();
	}

	private StringBuffer wrapJoberInfoReqParams(StringBuffer sql, Map<String, Object> map, int level) {
		// 市-省-国 查询
		this.wrapLevelParam(sql, map, level);
		if (map.get("type_name") != null && !map.get("type_name").equals("")) {
			sql.append(" and type_name = #{type_name}");
		}
		if (map.get("subject") != null && StringUtils.isNotBlank(map.get("subject").toString())) {
			sql.append(" and subject like concat('%', #{subject}, '%')");
		}
		if (map.get("name") != null && StringUtils.isNotBlank(map.get("name").toString())) {
			sql.append(" and name like concat('%', #{name}, '%')");
		}
		if (map.get("phone") != null && StringUtils.isNotBlank(map.get("phone").toString())) {
			sql.append(" and phone like concat('%', #{phone}, '%')");
		}
		return sql;
	}
	public String getJoberInfo(Map<String, Object> map) {

		StringBuffer sql = new StringBuffer("select * from publish_jober_info where ");
		sql.append(" id= #{id}");
		return sql.toString();
	}

	public String updateJoberBrowse(Map<String, Object> map) {

		StringBuffer sql = new StringBuffer("update publish_jober_info set browse = (browse + FLOOR(60 + RAND() * (80 - 60))) ");
		sql.append(" where id= #{id}");
		return sql.toString();
	}

	public String publishRecruitInfo(Map<String,Object> reqMap) {
		StringBuffer sql = new StringBuffer("INSERT INTO publish_recruit_info (id, subject,company_name, contact, phone, work_address, equipment_type, drive_age, project_type, work_type, opreation_direction, salary, treatment , delete_flag , province , city  , area , position , create_user , create_time , details , remark , type_name , browse , education, examine_flag, is_updated_browse, tel_consult,is_company_publish) VALUES ( #{id},#{subject}, #{company_name}, #{contact}, #{phone}, #{work_address}, #{equipment_type}, #{drive_age}, #{project_type}, #{work_type}, #{opreation_direction}, #{salary},#{treatment},0,#{province},#{city},#{area},#{position},#{u_id},now(),#{details},#{remark},#{type_name},0,#{education},0,0,0");
		if(reqMap.get("isCompany").equals('0')){
			sql.append(",0)");
		}else if(reqMap.get("isCompany").equals('1')){
			sql.append(",1)");
		}
        return sql.toString();
	}

	public String updateRecruitExamineFlag(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("update publish_recruit_info set examine_flag = #{examine_flag}");
		if ("1".equals(map.get("examine_flag").toString())) {
			sql.append(", browse=FLOOR(100 + RAND() * (300 - 100)) ");
		}
		sql.append(", update_user = #{update_user}, update_time = #{update_time} where delete_flag = 0");
		if (map.get("ids") != null) {
			sql.append(" and find_in_set(id, #{ids}) ");
		}
		if (map.get("hour") != null && StringUtils.isNotBlank(map.get("hour").toString())) {
			sql.append(" and examine_flag = 0 ");
			sql.append(" and UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(publish_time) >= #{hour}");
		}
		return sql.toString();
	}

	public String getRecruitInfoList(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from publish_recruit_info where delete_flag = 0 and examine_flag = 1");
		this.wrapRecruitInfoReqParams(sql, map, 1);
		if (map.get("filter") != null) {
			if ("0".equals(map.get("filter").toString())) {
				sql.append(" order by create_time desc");
			} else if ("1".equals(map.get("filter").toString())) {
				sql.append(" order by browse desc");
			} else if ("2".equals(map.get("filter").toString())) {
				sql.append(" order by create_time desc");
			}
		} else if (map.get("isRecommend") != null) {
			sql.append(" and id != #{id}");
			sql.append(" order by create_time desc");
		}  else {
			sql.append(" order by create_time desc");
		}
		sql.append(" LIMIT #{currentPage}, #{pageSize}");
		return sql.toString();
	}

	public String getRecruitInfoCount(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from publish_recruit_info where delete_flag = 0 and examine_flag = 1");
		this.wrapRecruitInfoReqParams(sql, map, 1);
		return sql.toString();
	}
	private StringBuffer wrapRecruitInfoReqParams(StringBuffer sql, Map<String, Object> map, int level) {
		// 市-省-国 查询
		this.wrapLevelParam(sql, map, level);
		if (map.get("type_name") != null && !map.get("type_name").equals("")) {
			sql.append(" and type_name = #{type_name}");
		}
		return sql;
	}

	public String getRecruitInfo(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("select * from publish_recruit_info where ");
		sql.append("  id = #{id}");
		return sql.toString();
	}

	public String updateRecruitBrowse(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("update publish_recruit_info set browse = (browse + FLOOR(60 + RAND() * (80 - 60))) ");
		sql.append(" where id = #{id}");
		return sql.toString();
	}

	public String getCarTypeM(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer(
				"select id,parent_id,type_name from vehicle_type_new where parent_id = '0'" );
        if (map.get("keyword") != null && StringUtils.isNotBlank(map.get("keyword").toString())) {
            sql.append(" and type_name like '%"+ map.get("keyword") +"%' ");
        }

        sql.append(" order by recommend_status desc");
		return sql.toString();
	}

	public String getCarTypeS(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("select id,parent_id,type_name,img_path from vehicle_type_new where 1=1");
        if (map.get("keyword") != null && StringUtils.isNotBlank(map.get("keyword").toString())) {
            sql.append(" and type_name like '%"+ map.get("keyword") +"%' ");
        }
		sql.append(" and parent_id = #{parent_id}");
		return sql.toString();
	}

	public String getCarBrand(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("select * from vehicle_brand_new where 1=1");
		if (map.get("keyword") != null && StringUtils.isNotBlank(map.get("keyword").toString())) {
			sql.append(" and brand_name like '%"+ map.get("keyword") +"%' ");
		}
		sql.append(" and type_id = #{id}");
		return sql.toString();
	}

	public String getCarModel(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("select * from vehicle_model_new where 1=1");
		sql.append(" and brand_id = #{id}");
		return sql.toString();
	}

	public String getCarInfo(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer(
				"SELECT t2.*, t1.param_value FROM vehicle_param_value t1 LEFT JOIN vehicle_param_template t2 ON t1.param_id = t2.id where ");
		sql.append(" model_id = #{id}");
		return sql.toString();
	}

	public String getMould(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer(
				"SELECT t4.* FROM ( SELECT t6.parent_id FROM vehicle_param_value t5 LEFT JOIN vehicle_param_template t6 ON t5.param_id = t6.id 	WHERE t5.model_id = #{id} GROUP BY t6.parent_id ) t3 LEFT JOIN vehicle_param_template t4 ON t4.id = t3.parent_id");
		return sql.toString();
	}

	public String getPublicCarInfo(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer();
		String columns = " t1.* ";
		if ((map.get("isMap") != null && Boolean.valueOf(map.get("isMap").toString()))) {
			columns = " t1.id, t1.longitude, t1.latitude, t1.model, t1.price  ";
		}

		sql.append("select " + columns + " from zowe_buyinfo_detail t1 ") ;
		if ((map.get("classification") != null && "2".equals(map.get("classification").toString()))
				&& ((map.get("isCarMasterOrDriver") != null && (boolean) map.get("isCarMasterOrDriver"))
				|| (map.get("isServer") != null && (boolean) map.get("isServer")))) {
            sql.append(" left join t_s_ss_user t2 on t2.id = t1.create_user and t2.delete_flag = 0 ");
        }
		sql.append(" where t1.type = #{type} and t1.delete_flag = 0 and t1.examine_flag = 1 ");
		this.wrapPublicCarInfoReqParams(sql, map, 1);
		if ((map.get("classification") != null && "2".equals(map.get("classification").toString()))
				&& ((map.get("isCarMasterOrDriver") != null && (boolean) map.get("isCarMasterOrDriver"))
				|| (map.get("isServer") != null && (boolean) map.get("isServer")))) {
			sql.append(" and (t2.r_id != '"+ Constants.ROLE_CZ +"' and t2.r_id != '"+ Constants.ROLE_SJ +"')");
			sql.append(" and t2.s_id = '"+ map.get("s_id") +"'" );
		}
		if (map.get("ids") != null && StringUtils.isNotBlank(map.get("ids").toString())) {
			sql.append(" and find_in_set(t1.id, #{ids}) ");
		}
		if (map.get("price") != null && StringUtils.isNotBlank(map.get("price").toString()) && !"0".equals(map.get("price").toString())) {
			sql.append(" and ((trim(substr(t1.price, 1, position('万' in t1.price) - 1)) >= " + map.get("priceS") + " ");
			if (StringUtils.isNotBlank(map.get("priceE").toString())) {
				sql.append(" and trim(substr(t1.price, 1, position('万' in t1.price) - 1)) < " + map.get("priceE"));
			}
			sql.append(" )  or (trim(substr(t1.price, 1, position('元' in t1.price) - 1)) >= " + map.get("priceS") + "0000 ");
			if (StringUtils.isNotBlank(map.get("priceE").toString())) {
				sql.append(" and trim(substr(t1.price, 1, position('元' in t1.price) - 1)) < " + map.get("priceE") + "0000 ");
			}
			sql.append(" )  or (t1.price >= " + map.get("priceS") + "0000 ");
			if (StringUtils.isNotBlank(map.get("priceE").toString())) {
				sql.append(" and t1.price < " + map.get("priceE") + "0000 ");
			}
			sql.append(" and (position('万' in t1.price) = 0 or position('万' in t1.price) is null) " +
					" and (position('元' in t1.price) = 0 or position('元' in t1.price) is null))) ");
			if (StringUtils.isNotBlank(map.get("priceS").toString()) || StringUtils.isNotBlank(map.get("priceE").toString())) {
				sql.append("and t1.price != '面议'");
			}
		}
		sql.append(" order by IFNULL(t1.is_promotion,2), t1.create_time desc");

		if (map.get("currentPage") != null && map.get("ids") == null) {
			sql.append(" LIMIT #{currentPage}, #{pageSize}");
		}
		return sql.toString();
	}

	public String getPublicCarInfoTotal(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(id) from ( ");
		sql.append("select * from zowe_buyinfo_detail t where type = #{type} and delete_flag = 0 and examine_flag = 1 ");
		this.wrapPublicCarInfoReqParams(sql, map, 1);
		return sql.toString();
	}
	private StringBuffer wrapPublicCarInfoReqParams(StringBuffer sql, Map<String, Object> map, int level) {
		// 市-省-国 查询
		this.wrapLevelParam(sql, map, level);
		if (map.get("classification") != null && !map.get("classification").equals("")) {
			sql.append(" and t1.classification = #{classification}");
		}
		if (map.get("is_flag") != null && !map.get("is_flag").equals("")) {
			sql.append(" and t1.is_flag = #{is_flag}");
		}
		if (map.get("brand") != null && StringUtils.isNotBlank(map.get("brand").toString())) {
			sql.append(" and find_in_set(t1.brand, #{brand}) ");
		}
		if (map.get("model") != null && StringUtils.isNotBlank(map.get("model").toString())) {
			sql.append(" and find_in_set(t1.model, #{model}) ");
		}
		if (map.get("parent_type_name") != null && !map.get("parent_type_name").equals("")) {
			sql.append(" and t1.mechanics_parent_type_name = #{parent_type_name}");
		}
		String type = map.get("type").toString();
		if ("求购".equals(type)) {
			if (map.get("rental_method") != null && StringUtils.isNotBlank(map.get("rental_method").toString())) {
				sql.append(" and t1.model = #{rental_method} ");
			}
		} else if ("求租".equals(type)){
			if (map.get("rental_method") != null && StringUtils.isNotBlank(map.get("rental_method").toString())) {
				sql.append(" and t1.rental_method = #{rental_method} ");
			}
		} else {
			if (map.get("rental_method") != null && StringUtils.isNotBlank(map.get("rental_method").toString())) {
				sql.append(" and t1.rental_method = #{rental_method} ");
			}
		}
		if (map.get("type_name") != null && !map.get("type_name").equals("")) {
			sql.append(" and t1.mechanics_type_name = #{type_name}");
		}
		if (map.get("subject") != null && StringUtils.isNotBlank(map.get("subject").toString())) {
			sql.append(" and t1.subject like concat('%', #{subject}, '%')");
		}
		if (map.get("lianxiren") != null && StringUtils.isNotBlank(map.get("lianxiren").toString())) {
			sql.append(" and t1.lianxiren like concat('%', #{lianxiren}, '%')");
		}
		if (map.get("phone") != null && StringUtils.isNotBlank(map.get("phone").toString())) {
			sql.append(" and t1.phone like concat('%', #{phone}, '%')");
		}
		if (map.get("is_promotion") != null && StringUtils.isNotBlank(map.get("is_promotion").toString())) {
			sql.append(" and t1.is_promotion = #{is_promotion}");
		}
		return sql;
	}
	public String getPublicCarDetail(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("select t1.*, t3.model_id, t3.brand_id, t3.parent_model_id, t3.parent_brand_id from zowe_buyinfo_detail t1");

		sql.append(" left join zowe_buyinfo_detail_brand_model t3 on t3.buyinfo_id = t1.id ");
		sql.append("where t1.id = #{id}");
		return sql.toString();
	}

	public String updateBuyInfoBrowse(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("update zowe_buyinfo_detail set browse = (browse + FLOOR(60 + RAND() * (80 - 60))) ");
		sql.append(" where id = #{id}");
		return sql.toString();
	}

	public String addPublicCarDetail(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("insert into ");
		if(map.get("classification") != null && map.get("classification").equals("2")) {
			sql.append("t_s_ss_new_machine");
		}else {
			sql.append("zowe_buyinfo_detail");
		}
		sql.append(
				"(id,subject,type,lianxiren,phone,price,caraddress,image,imagefirst,cardiscription,create_user,create_time,videopath,quantity,hour,factory_time,age_limit,company_name,time_project,payment,brand,model,model_number,url_null_or_void,is_urgent,province,city,area,thumFileName,thum_videopath,mechanics_parent_type_name,mechanics_type_name,brand_name,attribute_name,sale_state,work_address,rental_method,delete_flag,browse,examine_flag, is_updated_browse,score,tel_consult,required_model,is_company_publish,classification,detail_picture,is_promotion,promotion_detail)");
		sql.append(
				"value( #{id}, #{subject}, #{type}, #{lianxiren}, #{phone},  #{price}, #{caraddress}, #{image},#{imagefirst}, #{cardiscription}, #{create_user}, now(),#{videopath},#{quantity},#{hour},#{factory_time},#{age_limit},#{company_name},#{time_project},#{payment},#{brand},#{model},#{model_number},1,#{is_urgent},#{province},#{city},#{area},#{thumFileName},#{thum_videopath},#{mechanics_parent_type_name},#{mechanics_type_name},#{brand_name},#{attribute_name},#{sale_state},#{work_address},#{rental_method},0,0,#{examine_flag},0,5,0,#{required_model}");
		if(map.get("isCompany").equals('0')) {
			sql.append(",0");
		} else if(map.get("isCompany").equals('1')) {
			sql.append(",1");
		}
		sql.append(",#{classification},#{detail_picture},#{is_promotion},#{promotion_detail}");
		sql.append(")");
		return sql.toString();
	}

	public String updateCarDetailExamineFlag(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("update zowe_buyinfo_detail set examine_flag = #{examine_flag}");
		if ("1".equals(map.get("examine_flag").toString())) {
			sql.append(", browse=FLOOR(100 + RAND() * (300 - 100)) ");
		}
		sql.append("  where delete_flag = 0");
		if (map.get("ids") != null) {
			sql.append(" and find_in_set(id, #{ids}) ");
		}
		if (map.get("hour") != null && StringUtils.isNotBlank(map.get("hour").toString())) {
			sql.append(" and examine_flag = 0 ");
			sql.append(" and UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(publish_time) >= #{hour}");
		}
		return sql.toString();
	}

	public String getPublicCarInfoByLatlong(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("select * from zowe_buyinfo_detail where delete_flag = 0 ");
		sql.append(" and type = #{type}");
		sql.append(" and caraddress like #{caraddress}");
		sql.append(" order by fnGetDistance(#{latitude},#{longitude},latitude,longitude) asc");
		sql.append(" LIMIT #{currentPage}, #{pageSize}");
		return sql.toString();

	}

	public String getProvinceByNumber(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer(
				"SELECT t1.number as value,t1.province_name as label FROM t_s_ss_province t1");
		return sql.toString();
	}

	public String getCityByNumber(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("SELECT t1.number as value,t1.city_name as label FROM t_s_ss_city t1");
		sql.append(" where t1.pid= #{number}");
		return sql.toString();
	}

	public String getAreaByNumber(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer(
				"SELECT t1.number as value,t1.area_name as label FROM t_s_ss_area t1 WHERE");
		sql.append("t1.p_number= #{number}");
		return sql.toString();
	}

	public String getMallList(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("SELECT id,model_name FROM vehicle_model_new WHERE ");
		sql.append(" model_name like #{model_name}");
		sql.append(" LIMIT #{currentPage}, #{pageSize}");
		return sql.toString();

	}

	public String getMallListSize(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("SELECT COUNT(model_name) FROM vehicle_model_new WHERE");
		sql.append(" model_name like #{model_name}");
		return sql.toString();
	}
	
	public String getNewsList(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("SELECT uuid,title,description,date_time,img_name,browse FROM hot_news ");

		sql.append(" order by date_time desc");

		sql.append(" LIMIT #{currentPage}, #{pageSize}");
		return sql.toString();
	}
	public String updateBrowseForHotNews(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("update hot_news set browse = browse + 1 where uuid = #{id}");
		return sql.toString();
	}
	
	public String getNewsListCount(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("SELECT COUNT(uuid) FROM hot_news ORDER BY date_time DESC");
		return sql.toString();
	}
	
	public String getNewsDetail(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("SELECT n.uuid as id, n.browse,d.news_uuid,d.title,d.date_time,d.content,n.description FROM " +
				"hot_news n left join hot_news_detail d on n.uuid = d.news_uuid ");
		sql.append(" WHERE n.uuid = #{uuid}");
		return sql.toString();
	}

	public String getModelBySubTypeId(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("select * from mechanics_type where delete_flag=0 ");
		sql.append(" and parent_id = #{type_id}");
		return sql.toString();
	}

	public String getBrandByTypeId(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("select b.* from mechanics_type_brand tb left join mechanics_brand b on tb.brand_id = b.id and b.delete_flag=0 ");
		sql.append(" where tb.type_id = #{type_id}");
		return sql.toString();
	}

	public String getBigAttributeByTypeId(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("select a.* from mechanics_type_attribute ta left join mechanics_attribute a on ta.attribute_id = a.id and a.delete_flag = 0 and a.parent_id = '0'");
		sql.append(" where ta.type_id = #{type_id}");
		return sql.toString();
	}

	public String getSubAttribute(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("select * from mechanics_attribute where delete_flag = 0 ");
		sql.append(" and parent_id = #{id}");
		return sql.toString();
	}

	public String getHotBrand() {
		StringBuffer sql = new StringBuffer("select * from mechanics_brand where delete_flag = 0 and hot_brand = 1");
		return sql.toString();
	}

	public String getHotMechanicsType() {
		StringBuffer sql = new StringBuffer("select * from mechanics_type where delete_flag = 0 and hot_model = 1");
		return sql.toString();
	}

	public String getBigMechanicsType() {
		StringBuffer sql = new StringBuffer("select * from mechanics_type where delete_flag = 0 and parent_id = '0'");
		return sql.toString();
	}

	public String getSubMechanicsType(Map<String, Object> map) {
		List<String> ids = (List<String>) map.get("ids");
		StringBuffer sql = new StringBuffer("select * from mechanics_type where delete_flag = 0");
		sql.append(" and parent_id in (");
		for (int i=0; i<ids.size(); i++) {
			sql.append("\""+ ids.get(i) + "\"");
			if (i < ids.size()-1) {
				sql.append(",");
			}
		}
		sql.append(")");
		return sql.toString();
	}

	public String getMechanicsType(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("select * from mechanics_type where delete_flag = 0 and type_level = '2'");
        if (map.get("searchModel") != null && StringUtils.isNotBlank(map.get("searchModel").toString())) {
            sql.append(" and type_name like concat('%', #{searchModel},'%')");
        }
        return sql.toString();
	}

	public String getMechanics(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("select * from zowe_buyinfo_detail ");
		sql.append(" where ( type = \"出售\" or type = \"出租\" ) and delete_flag = 0");
		if (map.get("mechanics_parent_type_name") != null && !map.get("mechanics_parent_type_name").equals("")) {
			sql.append(" and mechanics_parent_type_name = #{mechanics_parent_type_name}");
		}
		if (map.get("mechanics_type_name") != null && !map.get("mechanics_type_name").equals("")) {
			sql.append(" and mechanics_type_name = #{mechanics_type_name}");
		}
		if (map.get("brand_name") != null && !map.get("brand_name").equals("")) {
			sql.append(" and brand_name = #{brand_name}");
		}
		if (map.get("attribute_name") != null && !map.get("attribute_name").equals("")) {
			sql.append(" and attribute_name = #{attribute_name}");
		}
		if (map.get("sale_state") != null && !map.get("sale_state").equals("")) {
			sql.append(" and sale_state = #{sale_state}");
		}
		if (map.get("visits_number") != null && !map.get("visits_number").equals("")) {
			sql.append(" order by visits_number desc");
		}
		if (map.get("createtime") != null && !map.get("createtime").equals("")) {
			sql.append(" order by create_time desc");
		}
		sql.append(" LIMIT #{currentPage}, #{pageSize}");
		return sql.toString();
	}

	public String getCountMechanics(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("select count(*) from zowe_buyinfo_detail ");
		sql.append(" where ( type = \"出售\" or type = \"出租\" ) and delete_flag = 0 ");
		if (map.get("mechanics_parent_type_name") != null && !map.get("mechanics_parent_type_name").equals("")) {
			sql.append(" and mechanics_parent_type_name = #{mechanics_parent_type_name}");
		}
		if (map.get("mechanics_type_name") != null && !map.get("mechanics_type_name").equals("")) {
			sql.append(" and mechanics_type_name = #{mechanics_type_name}");
		}
		if (map.get("brand_name") != null && !map.get("brand_name").equals("")) {
			sql.append(" and brand_name = #{brand_name}");
		}
		if (map.get("attribute_name") != null && !map.get("attribute_name").equals("")) {
			sql.append(" and attribute_name = #{attribute_name}");
		}
		if (map.get("sale_state") != null && !map.get("sale_state").equals("")) {
			sql.append(" and sale_state = #{sale_state}");
		}
		if (map.get("visits_number") != null && !map.get("visits_number").equals("")) {
			sql.append(" order by visits_number desc");
		}
		return sql.toString();
	}

	public String getInquiryInfo(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("select * from zowe_floor_price where delete_flag = 0");
		sql.append(" and b_id = #{b_id}");
		sql.append(" order by create_time desc");
		sql.append(" LIMIT 0,10");
		return sql.toString();
	}

	public String getInquiryInfoCount(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("select visits_number from zowe_buyinfo_detail ");
		sql.append(" where id = #{b_id}");
		return sql.toString();
	}

	public String insertInquiry(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("insert into zowe_floor_price (id,name,phone,buy_address,car_status,b_id,province,city,delete_flag,c_id,user_name,create_time) " +
				" values (#{id},#{name},#{phone},#{buy_address},#{car_status},#{b_id},#{province},#{city},#{delete_flag},#{c_id},#{user_name},now())");
		return sql.toString();
	}

	public String updateInquiryInfoCount(Map<String, Object> reqMap) {
		StringBuffer sql = new StringBuffer("update zowe_buyinfo_detail set visits_number = #{inquiryInfoCount} ");
		sql.append(" where id = #{b_id}");
		return sql.toString();
	}

	public String insertMyCollection(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("insert into zowe_my_collection (id,u_id,u_name,o_id,type,subject,create_user,create_time,update_user,update_time,publish_user,classification) " +
				" values (#{id},#{u_id},#{u_name},#{o_id},#{type},#{subject},#{u_id},now(),#{update_user},#{update_time},#{create_user},1)");
		return sql.toString();
	}

	public String deleteMyCollection(Map<String, Object> reqMap) {
		StringBuffer sql = new StringBuffer("update zowe_my_collection set delete_flag = 1 ");
		sql.append(" where o_id = #{o_id} and u_id = #{u_id} and classification = 1");
		return sql.toString();
	}

	public String myCollectionSearch(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("select * from zowe_my_collection ");
		sql.append(" where u_id = #{u_id}");
		if (map.get("subject") != null && !map.get("subject").equals("")) {
			sql.append(" and subject like #{subject}");
		}
		sql.append(" and classification = 1 ");
		sql.append(" order by type desc,create_time desc");
		sql.append(" LIMIT #{currentPage}, #{pageSize}");
		return sql.toString();
	}

	public String getMyCollection(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("select * from zowe_my_collection ");
		sql.append(" where u_id = #{u_id}");
		if (map.get("o_id") != null && !map.get("o_id").equals("")) {
			sql.append(" and o_id = #{o_id}");
		}
		if (map.get("type") != null && !map.get("type").equals("")) {
			sql.append(" and type = #{type}");
		}
		sql.append(" and delete_flag = 0 ");
		sql.append(" and classification = 1 ");
		return sql.toString();
	}

	public String getTypeMyCollection(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("select * from zowe_my_collection ");
		sql.append(" where u_id = #{u_id}");
		if (map.get("type") != null && !map.get("type").equals("")) {
			sql.append(" and type = #{type}");
		}
		sql.append(" and delete_flag = 0 ");
		sql.append(" and classification = 1 ");
		sql.append(" order by create_time desc");
		sql.append(" LIMIT #{currentPage}, #{pageSize}");
		return sql.toString();
	}

	public String getMyCollectionCount(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("select count(*) from zowe_my_collection ");
		sql.append(" where u_id = #{u_id}");
		if (map.get("type") != null && !map.get("type").equals("")) {
			sql.append(" and type = #{type}");
		}
		if (map.get("subject") != null && !map.get("subject").equals("")) {
			sql.append(" and subject like #{subject}");
		}
		sql.append(" and delete_flag = 0 ");
		sql.append(" and classification = 1 ");
		sql.append(" order by create_time desc");
		return sql.toString();
	}
	public String collectionMap(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("select * from zowe_my_collection ");
		sql.append(" where id = #{id}");
		return sql.toString();
	}
	public String getNewMachineList(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer();
		String columns = " t1.* ";
		sql.append("select " + columns + " from t_s_ss_new_machine t1 ") ;
		if ((map.get("classification") != null && "2".equals(map.get("classification").toString()))
				&& ((map.get("isCarMasterOrDriver") != null && (boolean) map.get("isCarMasterOrDriver"))
				|| (map.get("isServer") != null && (boolean) map.get("isServer")))) {
			sql.append(" left join t_s_ss_user t2 on t2.id = t1.create_user and t2.delete_flag = 0 ");
		}
		sql.append(" where  t1.delete_flag = 0 and t1.examine_flag = 1 ");
		this.wrapPublicCarInfoReqParams(sql, map, 1);
		if ((map.get("classification") != null && "2".equals(map.get("classification").toString()))
				&& ((map.get("isCarMasterOrDriver") != null && (boolean) map.get("isCarMasterOrDriver"))
				|| (map.get("isServer") != null && (boolean) map.get("isServer")))) {
			sql.append(" and (t2.r_id != '"+ Constants.ROLE_CZ +"' and t2.r_id != '"+ Constants.ROLE_SJ +"')");
			sql.append(" and t2.s_id = '"+ map.get("s_id") +"'" );
		}
		if (map.get("ids") != null && StringUtils.isNotBlank(map.get("ids").toString())) {
			sql.append(" and find_in_set(t1.id, #{ids}) ");
		}
		sql.append(" order by is_promotion, t1.create_time desc");

		if (map.get("currentPage") != null && map.get("ids") == null) {
			sql.append(" LIMIT #{currentPage}, #{pageSize}");
		}
		return sql.toString();
	}

	public String getNewMachineDetail(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("select t1.*, t3.model_id, t3.brand_id, t3.parent_model_id, t3.parent_brand_id from t_s_ss_new_machine t1");

		sql.append(" left join zowe_buyinfo_detail_brand_model t3 on t3.buyinfo_id = t1.id ");
		sql.append("where t1.id = #{id}");
		return sql.toString();
	}

	public String updateNewMachineBrowser(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("update t_s_ss_new_machine set browse = (browse + FLOOR(60 + RAND() * (80 - 60))) ");
		sql.append(" where id = #{id}");
		return sql.toString();
	}

	public String updateZoweBuyinfoDetailForPushFlag(Map<String, Object> map) {
		StringBuffer sql = new StringBuffer("update zowe_buyinfo_detail set has_pushed = 1");
		sql.append(" where id = #{id}");
		return sql.toString();
	}
}
